package model.registro;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.banco.ConexaoFactory;
import model.nota.Nota;
import ModeloAcademico.Alunos;
import ModeloAcademico.ModelCurso;
import ModeloAcademico.ModeloTurma;
import ModeloAcademico.ProfessoeBeen;

public class RegistroNotasDAO {

	private Connection conexao;

	public RegistroNotasDAO() {
		conexao = ConexaoFactory.getInstance().getConnection();
	}

	public List<ProfessoeBeen> listarProfessores() {
		List<ProfessoeBeen> professores = new ArrayList<ProfessoeBeen>();
		String select = "SELECT p.id, p.nome FROM professor p";
		try {
			PreparedStatement statement = conexao.prepareStatement(select);
			ResultSet set = statement.executeQuery();
			while (set.next()) {
				ProfessoeBeen professor = new ProfessoeBeen();
				professor.setId(set.getLong("id"));
				professor.setNome(set.getString("nome"));
				professores.add(professor);
			}
			set.close();
			statement.close();
			return professores;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public List<ModeloTurma> listarTurmas(ProfessoeBeen professor) {
		List<ModeloTurma> turmas = new ArrayList<ModeloTurma>();
		String select = "SELECT curso.nome, turma.id, turma.descricao "
				+ "FROM curso, professor, turma, turma_professor "
				+ "WHERE professor.id = turma_professor.id_professor "
				+ "AND turma.id = turma_professor.id_turma "
				+ "AND curso.id = turma.id_curso AND professor.id = ?";
		try {
			PreparedStatement statement = conexao.prepareStatement(select);
			statement.setLong(1, professor.getId());
			ResultSet set = statement.executeQuery();
			while (set.next()) {
				ModeloTurma turma = new ModeloTurma();
				ModelCurso curso = new ModelCurso();
				curso.setNome(set.getString("nome"));
				turma.setCurso(curso);
				turma.setId(set.getLong("id"));
				turma.setDescricao(set.getString("descricao"));
				turmas.add(turma);
			}
			set.close();
			statement.close();
			return turmas;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<Alunos> listarAlunos(ModeloTurma turma) {
		List<Alunos> alunos = new ArrayList<Alunos>();
		String select = "SELECT aluno.matricula, aluno.nome "
				+ "FROM	turma, aluno " + "WHERE	aluno.id_turma = turma.id "
				+ "AND turma.id = ? " + "ORDER BY aluno.nome";
		try {
			PreparedStatement statement = conexao.prepareStatement(select);
			statement.setLong(1, turma.getId());
			ResultSet set = statement.executeQuery();
			while (set.next()) {
				Alunos aluno = new Alunos();
				aluno.setMatricula(set.getString("matricula").trim());
				aluno.setNome(set.getString("nome").trim());
				alunos.add(aluno);
			}
			set.close();
			statement.close();
			return alunos;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public TurmaProfessor carregarTurmaProfessor(ProfessoeBeen professor,
			ModeloTurma turma) {
		TurmaProfessor turmaProfessor = new TurmaProfessor();

		String select = "SELECT turma_professor.id "
				+ "FROM turma_professor, professor, turma "
				+ "WHERE turma.id = turma_professor.id_turma AND professor.id = turma_professor.id_professor "
				+ "AND professor.id = ? AND turma.id = ?";
		PreparedStatement statement;
		try {
			statement = conexao.prepareStatement(select);
			statement.setLong(1, professor.getId());
			statement.setLong(2, turma.getId());
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				turmaProfessor.setId(rs.getLong("id"));
			}
			rs.close();
			statement.close();
			return turmaProfessor;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Alunos carregarIdAluno(Alunos aluno) {
		String select = "SELECT id FROM aluno WHERE alunos.matricula = ?";
		try {
			PreparedStatement statement = conexao.prepareStatement(select);
			statement.setString(1, aluno.getMatricula());
			ResultSet set = statement.executeQuery();
			while (set.next()) {
				aluno.setId(set.getLong("id"));
			}
			set.close();
			statement.close();
			return aluno;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void salvarNotas(List<Nota> notas) {
		for (Nota nota : notas)
			salvarNota(nota);
	}

	private void salvarNota(Nota nota) {
		String insert = "INSERT INTO nota (id_aluno, id_turma_professor, descricao, valor) VALUES (?, ?, ?, ?)";
		PreparedStatement statement;

		try {
			statement = conexao.prepareStatement(insert);
			statement.setLong(1, nota.getAluno().getId());
			statement.setLong(2, nota.getTurmaProfessor().getId());
			statement.setString(3, nota.getDescricao());
			statement.setFloat(4, nota.getValor());
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<Alunos> carregarNota(List<Nota> notas){
		List<Alunos> alunos = new ArrayList<Alunos>();
		String select = "SELECT	alunos.matricula, nota.valor, nota.descricao " +
				"FROM	nota, alunos, turma_professor " +
				"WHERE	nota.id_aluno = alunos.id " +
				"AND	nota.id_turma_professor = turma_professor.id " +
				"AND  	alunos.id = ? " +
				"AND	turma_professor.id = ?";
		
		try {
			for (Nota nota : notas){
				PreparedStatement statement = conexao.prepareStatement(select);
				statement.setLong(1, nota.getAluno().getId());
				statement.setLong(2, nota.getTurmaProfessor().getId());
				ResultSet set = statement.executeQuery();
				while (set.next()) {
					Alunos aluno = new Alunos();
					aluno.setMatricula(set);
					nota.setValor(set.getFloat("valor"));
					nota.setDescricao(set.getString("descricao"));
					alunos.add(nota);
				}
				set.close();
				statement.close();
				
			}
			return alunos;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

}
